{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = pd.read_excel(\"test.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "table = table.fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "ST = r'''\\begin{center}\n",
    "    \\begin{table}[H]\n",
    "        \\begin{tabular}{|c|c|c|c|c|}\n",
    "            \\hline\n",
    "            \\multirow{4}{*}{合并两行两列}&A&B&\\multirow{4}{*}{和}&1.0\\\\\n",
    "            \\cline{2\n",
    "            -\n",
    "            3}\n",
    "            \\cline{5\n",
    "            -\n",
    "            5}\n",
    "            &C&D&&2.0\\\\\n",
    "            \\cline{2\n",
    "            -\n",
    "            3}\n",
    "            \\cline{5\n",
    "            -\n",
    "            5}\n",
    "            &E&F&&3.0\\\\\n",
    "            \\cline{2\n",
    "            -\n",
    "            3}\n",
    "            \\cline{5\n",
    "            -\n",
    "            5}\n",
    "            &G&H&&4.0\\\\\n",
    "            \\hline\n",
    "            &\\multicolumn{3}{c|}{三列一行}&\\\\\n",
    "            \\hline\n",
    "            &\\multirow{2}{*}{两行一列}&\\multicolumn{3}{c|}{不想听}\\\\\n",
    "            \\cline{1\n",
    "            -\n",
    "            1}\n",
    "            \\cline{3\n",
    "            -\n",
    "            5}\n",
    "            &&&真的&\\\\\n",
    "            \\hline\n",
    "            &\\multicolumn{2}{c|}{\\multirow{2}{*}{两行两列}}&&\\\\\n",
    "            \\cline{1\n",
    "            -\n",
    "            1}\n",
    "            \\cline{4\n",
    "            -\n",
    "            5}\n",
    "            &\\multicolumn{2}{c|}{}&&\\\\\n",
    "            \\hline\n",
    "        \\end{tabular}\n",
    "    \\end{table}\n",
    "\\end{center}'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "re_cline = re.compile(r\"(\\\\cline{[^}]*})\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = ST\n",
    "for st in re.findall(re_cline,ST):\n",
    "    res = res.replace(st,st.replace(\" \",\"\").replace(\"\\n\",\"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = re.sub(re_cline,lambda x:x.group(1).replace(\" \",\"\").replace(\"\\n\",\"\"),ST)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\\begin{center}\n",
      "    \\begin{table}[H]\n",
      "        \\begin{tabular}{|c|c|c|c|c|}\n",
      "            \\hline\n",
      "            \\multirow{4}{*}{合并两行两列}&A&B&\\multirow{4}{*}{和}&1.0\\\\\n",
      "            \\cline{2-3}\n",
      "            \\cline{5-5}\n",
      "            &C&D&&2.0\\\\\n",
      "            \\cline{2-3}\n",
      "            \\cline{5-5}\n",
      "            &E&F&&3.0\\\\\n",
      "            \\cline{2-3}\n",
      "            \\cline{5-5}\n",
      "            &G&H&&4.0\\\\\n",
      "            \\hline\n",
      "            &\\multicolumn{3}{c|}{三列一行}&\\\\\n",
      "            \\hline\n",
      "            &\\multirow{2}{*}{两行一列}&\\multicolumn{3}{c|}{不想听}\\\\\n",
      "            \\cline{1-1}\n",
      "            \\cline{3-5}\n",
      "            &&&真的&\\\\\n",
      "            \\hline\n",
      "            &\\multicolumn{2}{c|}{\\multirow{2}{*}{两行两列}}&&\\\\\n",
      "            \\cline{1-1}\n",
      "            \\cline{4-5}\n",
      "            &\\multicolumn{2}{c|}{}&&\\\\\n",
      "            \\hline\n",
      "        \\end{tabular}\n",
      "    \\end{table}\n",
      "\\end{center}\n"
     ]
    }
   ],
   "source": [
    "print(res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "empty:'' 0\n"
     ]
    }
   ],
   "source": [
    "k = sheet_0.cell(rowx=3,colx=6)\n",
    "print(k,k.ctype)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'ExcelFile' object has no attribute 'sheet_by_index'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-54-50cfe533f2d3>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      7\u001b[0m \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m     \u001b[0mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"don't yet know how to handle other excel file formats\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 9\u001b[1;33m \u001b[0msheet_0\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mexcel\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0msheet_by_index\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;31m# Open the first tab\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m     10\u001b[0m \u001b[0mdf\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mxls\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;36m0\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mheader\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mNone\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;31m#read the first tab as a datframe\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m     11\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'ExcelFile' object has no attribute 'sheet_by_index'"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "excel_file = \"test.xlsx\"\n",
    "if excel_file.endswith('xlsx'):\n",
    "    excel = pd.ExcelFile(xlrd.open_workbook(filepath), engine='xlrd')\n",
    "elif excel_file.endswith('xls'):\n",
    "    excel = pd.ExcelFile(xlrd.open_workbook(filepath, formatting_info=True), engine='xlrd')\n",
    "else:\n",
    "    print(\"don't yet know how to handle other excel file formats\")\n",
    "sheet_0 = excel.sheet_by_index(0) # Open the first tab\n",
    "df = xls.parse(0, header=None) #read the first tab as a datframe\n",
    "\n",
    "for e in sheet_0.merged_cells:\n",
    "    rl,rh,cl,ch = e\n",
    "    print(e)\n",
    "    base_value = sheet1.cell_value(rl, cl)\n",
    "    print(base_value)\n",
    "    df.iloc[rl:rh,cl:ch] = base_value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['__class__',\n",
       " '__delattr__',\n",
       " '__dict__',\n",
       " '__dir__',\n",
       " '__doc__',\n",
       " '__enter__',\n",
       " '__eq__',\n",
       " '__exit__',\n",
       " '__format__',\n",
       " '__fspath__',\n",
       " '__ge__',\n",
       " '__getattribute__',\n",
       " '__gt__',\n",
       " '__hash__',\n",
       " '__init__',\n",
       " '__init_subclass__',\n",
       " '__le__',\n",
       " '__lt__',\n",
       " '__module__',\n",
       " '__ne__',\n",
       " '__new__',\n",
       " '__reduce__',\n",
       " '__reduce_ex__',\n",
       " '__repr__',\n",
       " '__setattr__',\n",
       " '__sizeof__',\n",
       " '__str__',\n",
       " '__subclasshook__',\n",
       " '__weakref__',\n",
       " '_engines',\n",
       " '_io',\n",
       " '_reader',\n",
       " 'book',\n",
       " 'close',\n",
       " 'io',\n",
       " 'parse',\n",
       " 'sheet_names']"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
